package com.sltx.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;

/**
 * @Author Administrator
 * @Date 2022/3/2 10:55
 */
public class DBHelper {
    final String url = "jdbc:mysql://192.201.58.3:3306/sjs_qysj?serverTimezone=CST&autoReconnect=true&useUnicode=true&characterEncoding=utf-8&useSSL=false";
    final String dd = "com.mysql.jdbc.Driver";
    final String username = "qysj_rw";
    final String password = "jeeMot3rf5aqypI";
    Connection conn = null;
    Statement stmt = null;

    public DBHelper(){ }

    public void insertExcel(List<Map<String, Object>> list, String year, String month){
        try {
            Class.forName(dd);
            conn = DriverManager.getConnection(url,username,password);
            // 关闭自动执行
            conn.setAutoCommit(false);
            stmt = conn.createStatement();

            for (int i=0; i<list.size(); i++){
                String sql = "insert into f_sjs_qyss(entname, tax_cash, uni_scid) values ('"+
                        list.get(i).get("entname") +"','" +
                        list.get(i).get("tax_cash") + "','"+
                        list.get(i).get("uni_scid") + "');";
                stmt.addBatch(sql);
                if (i % 3 == 0 && i != 0){
                    stmt.executeBatch();
                    conn.commit();
                    stmt.clearBatch();
                }
            }
            stmt.executeBatch();
            conn.commit();
            stmt.clearBatch();
            close();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void close(){
        try {
            this.conn.close();
            this.stmt.close();
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
}
